Namespace persistent

    Public Class ServiceHistoryReport
        Public Shared Function Get_ActiveServices(ByVal p_custcode As String, ByRef l_rettable As DataTable, ByVal index As Integer) As String
            Dim sql As String
            'Dim l_percent As String
            'If p_custcode.Substring(0, 1) = "1" Then
            '    l_percent = ""
            'Else
            '    l_percent = "%"
            'End If
            Dim l_custcode As String
            If p_custcode.Substring(0, 1) = "1" Then
                l_custcode = "a.custcode = '[TAG_CUSTCODE]'"
            Else
                l_custcode = "(a.custcode = '[TAG_CUSTCODE]' or a.custcode like '[TAG_CUSTCODE].%')"
            End If
            l_custcode = l_custcode.Replace("[TAG_CUSTCODE]", p_custcode)
            sql = "select	/*+use_nl(dx)*/ " & _
vbNewLine & "	custcode, customer_id, co_id, msisdn, /*contract_status, 	 " & _
vbNewLine & "	sncode,*/ service_name, '''' || to_char(histno) || '' histno, service_status, status_date, 	 " & _
vbNewLine & "	tariff_name, last_situation, pending_state, 	 " & _
vbNewLine & "	fu_package_id, fu_package_name, fu_valid/*, ranking*/	 " & _
vbNewLine & "	from	 " & _
vbNewLine & "	(	 " & _
vbNewLine & "	    select /*+use_nl(a b c d e)*/ 	 " & _
vbNewLine & "	          a.custcode, 	 " & _
vbNewLine & "	          a.customer_id, 	 " & _
vbNewLine & "	          b.co_id, 	 " & _
vbNewLine & "	          fn_coid_to_msisdn (b.co_id) msisdn,	 " & _
vbNewLine & "	          upper(b.ch_status) contract_status,	 " & _
vbNewLine & "	          e.sncode, 	 " & _
vbNewLine & "	          e.des service_name, 	 " & _
vbNewLine & "	          h.histno, 	 " & _
vbNewLine & "	          h.status service_status, 	 " & _
vbNewLine & "	          h.valid_from_date status_date, 	 " & _
vbNewLine & "	          (select des from mputmtab t1 where tmcode =  	 " & _
vbNewLine & "	            (select tmcode from rateplan_hist t2 where t2.co_id = h.co_id and t2.tmcode_date = 	 " & _
vbNewLine & "	                (select max(tmcode_date) from rateplan_hist t3 where t3.co_id = h.co_id and t3.tmcode_date <= h.valid_from_date) 	 " & _
vbNewLine & "	            ) 	 " & _
vbNewLine & "	            and vscode = (select max(vscode) from mputmtab t4 where t4.tmcode = t1.tmcode) 	 " & _
vbNewLine & "	          )tariff_name, 	 " & _
vbNewLine & "	          case when c.status_histno = h.histno then 'LAST_SITUATION' else null end last_situation, 	 " & _
vbNewLine & "	          case when c.status_histno = h.histno then 	 " & _
vbNewLine & "	          ( 	 " & _
vbNewLine & "	               select p.status from mdsrrtab m, pr_serv_status_hist p 	 " & _
vbNewLine & "	               where m.request = p.request_id 	 " & _
vbNewLine & "	               and p.co_id = m.co_id 	 " & _
vbNewLine & "	               and p.valid_from_date is null 	 " & _
vbNewLine & "	               and m.co_id = c.co_id 	 " & _
vbNewLine & "	               and p.profile_id = c.profile_id 	 " & _
vbNewLine & "	               and p.sncode = c.sncode 	 " & _
vbNewLine & "	          ) 	 " & _
vbNewLine & "	          else null end pending_state,	 " & _
vbNewLine & "	          decode 	 " & _
vbNewLine & "	          ( 	 " & _
vbNewLine & "	              c.sncode, 	 " & _
vbNewLine & "	              1016, 	 " & _
vbNewLine & "	              ( 	 " & _
vbNewLine & "	                  select rpe_pack_id from rpe_pack rpe, parameter_value pv where rpe.rpe_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id 	 " & _
vbNewLine & "	              ), 	 " & _
vbNewLine & "	              53, 	 " & _
vbNewLine & "	              ( 	 " & _
vbNewLine & "	                  select fu_pack_id from fu_pack fp, parameter_value pv where fp.fu_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id 	 " & _
vbNewLine & "	              ) 	 " & _
vbNewLine & "	          ) fu_package_id 	 " & _
vbNewLine & "	          , 	 " & _
vbNewLine & "	          decode 	 " & _
vbNewLine & "	          ( 	 " & _
vbNewLine & "	              c.sncode, 	 " & _
vbNewLine & "	              1016, 	 " & _
vbNewLine & "	              ( 	 " & _
vbNewLine & "	                  select long_desc from rpe_pack rpe, parameter_value pv where rpe.rpe_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id 	 " & _
vbNewLine & "	              ), 	 " & _
vbNewLine & "	              53, 	 " & _
vbNewLine & "	              ( 	 " & _
vbNewLine & "	                  select long_name from fu_pack fp, parameter_value pv where fp.fu_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id 	 " & _
vbNewLine & "	              ) 	 " & _
vbNewLine & "	          ) fu_package_name,	 " & _
vbNewLine & "	          case when c.sncode in(53, 1016) then 	 " & _
vbNewLine & "	          eu_bpm.is_fu_valid	 " & _
vbNewLine & "	          (	 " & _
vbNewLine & "	                fn_coid_to_msisdn(c.co_id), 	 " & _
vbNewLine & "	                eu_bill.fn_get_rpe_pack_map	 " & _
vbNewLine & "	                (	 " & _
vbNewLine & "	                    ( 	 " & _
vbNewLine & "	                        select distinct prm_value_number from parameter_value pv 	 " & _
vbNewLine & "	                        where pv.co_id = c.co_id and pv.prm_value_id = c.prm_value_id 	 " & _
vbNewLine & "	                        and pv.prm_no = 	 " & _
vbNewLine & "	                        (	 " & _
vbNewLine & "	                            select max(pv2.prm_no) from parameter_value pv2 	 " & _
vbNewLine & "	                            where pv2.co_id = pv.co_id 	 " & _
vbNewLine & "	                            and pv2.prm_value_id = pv.prm_value_id	 " & _
vbNewLine & "	                        )	 " & _
vbNewLine & "	                    )	 " & _
vbNewLine & "	                )	 " & _
vbNewLine & "	          )	 " & _
vbNewLine & "	          else ''--'NOT_FU_SERVICE'	 " & _
vbNewLine & "	          end fu_valid	 " & _
vbNewLine & "	          ,rank() over(partition by h.co_id, h.sncode, h.profile_id order by h.valid_from_date desc, h.histno desc) ranking	 " & _
vbNewLine & "	    from customer_all a, contract_all b, profile_service c, pr_serv_status_hist h, mpusntab e	 " & _
vbNewLine & "	    where     [TAG_CUSTCODE] " & _
vbNewLine & "	          and a.customer_id = b.customer_id 	 " & _
vbNewLine & "	          and b.co_id = c.co_id 	 " & _
vbNewLine & "	          and c.sncode = e.sncode 	 " & _
vbNewLine & "	          and h.profile_id = c.profile_id 	 " & _
vbNewLine & "	          and h.co_id = c.co_id 	 " & _
vbNewLine & "	          and h.sncode = c.sncode	'[TAG3]' " & _
vbNewLine & "	          and h.status != 'O'	 " & _
vbNewLine & "	    --order by contract_status, b.co_id, c.sncode--, ranking	 " & _
vbNewLine & "	)dx	 " & _
vbNewLine & "	where 1=1	 " & _
vbNewLine & "	--and contract_status = 'A' 	 " & _
vbNewLine & "	and service_status = 'A'	 " & _
vbNewLine & "	and ranking = 1	 " & _
vbNewLine & "	order by contract_status, co_id, sncode, status_date--, ranking  "


            sql = sql.Replace("[TAG_CUSTCODE]", l_custcode)
            'sql = sql.Replace("[TAG2]", l_percent)

            If index > 0 Then
                sql = sql.Replace("'[TAG3]'", "and e.sncode=" & index)
            Else
                sql = sql.Replace("'[TAG3]'", " ")
            End If
            'LOGLAMA
            ' l_rettable = myDBConn.myORATable(sql)
            Dim gen As Object = New Web.page.ServiceHistoryReport()
            l_rettable = myDBConn.myORATable_LOG(sql, p_custcode, "", "", "", "Select(Get_ActiveServices)", "ServiceHistoryReport", gen.Session("user"))
            Return "OK"
        End Function

        Public Shared Function Get_Service_History(ByVal p_custcode As String, ByRef l_rettable As DataTable, ByVal index As Integer) As String
            Dim sql As String
            'Dim l_percent As String
            'If p_custcode.Substring(0, 1) = "1" Then
            '    l_percent = ""
            'Else
            '    l_percent = "%"
            'End If
            Dim l_custcode As String
            If p_custcode.Substring(0, 1) = "1" Then
                l_custcode = "a.custcode = '[TAG_CUSTCODE]'"
            Else
                l_custcode = "(a.custcode = '[TAG_CUSTCODE]' or a.custcode like '[TAG_CUSTCODE].%')"
            End If
            l_custcode = l_custcode.Replace("[TAG_CUSTCODE]", p_custcode)

            sql = "SELECT /*+use_nl(a b c d e)*/ " & _
   vbNewLine + "      a.custcode, " & _
   vbNewLine + "      a.customer_id, " & _
   vbNewLine + "      b.co_id, " & _
   vbNewLine + "      fn_coid_to_msisdn (b.co_id) msisdn, " & _
   vbNewLine + "      e.des service_name, " & _
   vbNewLine + "      h.histno, " & _
   vbNewLine + "      h.status service_status, " & _
   vbNewLine + "      h.valid_from_date status_date, " & _
   vbNewLine + "      (select des from mputmtab t1 where tmcode =  " & _
   vbNewLine + "        (select tmcode from rateplan_hist t2 where t2.co_id = h.co_id and t2.tmcode_date = " & _
   vbNewLine + "            (select min(tmcode_date) from rateplan_hist t3 where t3.co_id = h.co_id and t3.tmcode_date >= h.valid_from_date) " & _
   vbNewLine + "        ) " & _
   vbNewLine + "        and vscode = (select max(vscode) from mputmtab t4 where t4.tmcode = t1.tmcode) " & _
   vbNewLine + "      )tariff_name, " & _
   vbNewLine + "      case when c.status_histno = h.histno then 'LAST_SITUATION' else null end last_situation, " & _
   vbNewLine + "      case when c.status_histno = h.histno then " & _
   vbNewLine + "      ( " & _
   vbNewLine + "           SELECT p.status FROM MDSRRTAB m, PR_SERV_STATUS_HIST p " & _
   vbNewLine + "           WHERE m.REQUEST = p.REQUEST_ID " & _
   vbNewLine + "           AND p.CO_ID = m.CO_ID " & _
   vbNewLine + "           AND p.VALID_FROM_DATE IS NULL " & _
   vbNewLine + "           AND m.CO_ID = c.co_id " & _
   vbNewLine + "           AND p.PROFILE_ID = c.profile_id " & _
   vbNewLine + "           AND p.SNCODE = c.sncode " & _
   vbNewLine + "      ) " & _
   vbNewLine + "      else null end pending_state " & _
   vbNewLine + "      /*c.cs_stat_chng service_history*/ " & _
   vbNewLine + "      , " & _
   vbNewLine + "      decode " & _
   vbNewLine + "      ( " & _
   vbNewLine + "          c.sncode, " & _
   vbNewLine + "          1016, " & _
   vbNewLine + "          ( " & _
   vbNewLine + "              select rpe_pack_id from rpe_pack rpe, parameter_value pv where rpe.rpe_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id " & _
   vbNewLine + "          ), " & _
   vbNewLine + "          53, " & _
   vbNewLine + "          ( " & _
   vbNewLine + "              select fu_pack_id from fu_pack fp, parameter_value pv where fp.fu_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id " & _
   vbNewLine + "          ) " & _
   vbNewLine + "      ) fu_package_id " & _
   vbNewLine + "      , " & _
   vbNewLine + "      decode " & _
   vbNewLine + "      ( " & _
   vbNewLine + "          c.sncode, " & _
   vbNewLine + "          1016, " & _
   vbNewLine + "          ( " & _
   vbNewLine + "              select long_desc from rpe_pack rpe, parameter_value pv where rpe.rpe_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id " & _
   vbNewLine + "          ), " & _
   vbNewLine + "          53, " & _
   vbNewLine + "          ( " & _
   vbNewLine + "              select long_name from fu_pack fp, parameter_value pv where fp.fu_pack_id = pv.prm_value_number and pv.prm_value_id = c.prm_value_id " & _
   vbNewLine + "          ) " & _
   vbNewLine + "      ) fu_package_name " & _
   vbNewLine + "FROM customer_all a, contract_all b, profile_service c, PR_SERV_STATUS_HIST h, mpusntab e--, mputmtab d " & _
   vbNewLine + "WHERE     [TAG_CUSTCODE] " & _
   vbNewLine + "      AND a.customer_id = b.customer_id " & _
   vbNewLine + "      AND b.co_id = c.co_id " & _
   vbNewLine + "      AND c.sncode = e.sncode " & _
   vbNewLine + "      and h.profile_id = c.profile_id " & _
   vbNewLine + "      and h.co_id = c.co_id " & _
   vbNewLine + "      and h.sncode = c.sncode '[TAG3]'" & _
   vbNewLine + "      and h.status != 'O'" & _
   vbNewLine + "ORDER BY b.co_id, c.sncode, h.histno"

            sql = sql.Replace("[TAG_CUSTCODE]", l_custcode)
            'sql = sql.Replace("[TAG2]", l_percent)

            If index > 0 Then
                sql = sql.Replace("'[TAG3]'", "and e.sncode=" & index)
            Else
                sql = sql.Replace("'[TAG3]'", " ")
            End If
            'LOGLAMA
            ' l_rettable = myDBConn.myORATable(sql)
            Dim gen As Object = New Web.page.ServiceHistoryReport()
            l_rettable = myDBConn.myORATable_LOG(sql, p_custcode, "", "", "", "Select(Get_Service_History)", "ServiceHistoryReport", gen.Session("user"))
            Return "OK"
        End Function

        Public Shared Function Get_Customer_Contract_Report(ByVal p_custcode As String, ByRef l_rettable As DataTable) As String
            Dim sql As String
            Dim l_custcode As String
            If p_custcode.Substring(0, 1) = "1" Then
                l_custcode = "a.custcode = '[TAG_CUSTCODE]'"
            Else
                l_custcode = "(a.custcode = '[TAG_CUSTCODE]' or a.custcode like '[TAG_CUSTCODE].%')"
            End If
            l_custcode = l_custcode.Replace("[TAG_CUSTCODE]", p_custcode)
            sql = "select '[' || custcode || ']' custcode, a.cstype Customer_Status, cslevel, b.ccfname CONTACT_NAME, b.ccname COMPANY_NAME,  " & _
            vbNewLine + "        b.ccstreet || ' ' || b.ccstreetno || ' ' || b.ccaddr1 || ' ' || b.ccaddr2 || ' '  " & _
            vbNewLine + "        || b.ccaddr3 || b.cczip || ' ' || b.cccity || ' ' || e.name COMPANY_ADRESS, " & _
            vbNewLine + "        '[' || b.CCTN_AREA || b.CCTN || ']' Primary_Tel, '[' || b.CCTN2_AREA || b.CCTN2 || ']' Secondary_Tel,  " & _
            vbNewLine + "        (select SEGMENT_ACK from billintra.segment_sme y, billintra.segment_ack z where y.custcode = a.custcode and y.segment_id = z.segment_id) segment_name,  " & _
            vbNewLine + "        sysadm.fn_get_customer_type(a.customer_id) Customer_Type,  " & _
            vbNewLine + "        '[' || c.co_id || ']' co_id,  " & _
            vbNewLine + "        '[' || fn_coid_to_msisdn(c.co_id) || ']' MSISDN,  " & _
            vbNewLine + "        (select ch_status from curr_co_status x where x.co_id = c.co_id) contract_last_status,  " & _
            vbNewLine + "        (select to_char(ch_validfrom, 'dd.mm.yyyy hh24:mi:ss') from curr_co_status x where x.co_id = c.co_id) contract_last_status_date,  " & _
            vbNewLine + "        (select '[' || sm_serialnum || ']' from storage_medium x where x.sm_id = (select y.sm_id from port y where y.port_id = (select z.port_id from contr_devices z where z.co_id = c.co_id and z.cd_seqno = (select max(t.cd_seqno) from contr_devices t where t.co_id = z.co_id)))) ICCID,  " & _
            vbNewLine + "        '[' || EU_BILL.fn_custcode_to_dealercode((select u.custcode from customer_all u where u.customer_id = (select dealer_id from storage_medium x where x.sm_id = (select y.sm_id from port y where y.port_id = (select z.port_id from contr_devices z where z.co_id = c.co_id and z.cd_seqno = (select max(t.cd_seqno) from contr_devices t where t.co_id = z.co_id)))))) || ']' Dealer_Code,  " & _
            vbNewLine + "        (select des from mputmtab x where x.tmcode = c.tmcode and x.vscode = 0) Tariff_name,  " & _
            vbNewLine + "        (select rs_desc from reasonstatus_all x where x.rs_id = (select y.ch_reason from contract_history y where y.co_id = c.co_id and y.CH_SEQNO = 2)) activation_reason  " & _
            vbNewLine + "    from customer_all a, ccontact_all b, contract_all c , CCONTACT d, country e, billing_account ba " & _
            vbNewLine + "    where [TAG_CUSTCODE]  " & _
            vbNewLine + "        and a.customer_id = b.customer_id  " & _
            vbNewLine + "        and b.CCBILL = 'X'  " & _
            vbNewLine + "        and a.customer_id = c.customer_id (+)  " & _
            vbNewLine + "        and d.customer_id = b.customer_id " & _
            vbNewLine + "        and d.ccseq = b.ccseq " & _
            vbNewLine + "        and E.COUNTRY_ID (+)= d.country " & _
            vbNewLine + "        and " & GetCCBILLcondition("b", "ba")
            'vbNewLine + "        and " & persistent.common.GetCCBILLcondition("b", "ba"))
            sql = sql.Replace("[TAG_CUSTCODE]", l_custcode)
            'sql = sql.Replace("[TAG_YUZDE]", l_percent)
            'LOGLAMA
            'l_rettable = myDBConn.myORATable(sql)
            Dim gen As Object = New Web.page.ServiceHistoryReport()
            l_rettable = myDBConn.myORATable_LOG(sql, p_custcode, "", "", "", "Select(Get_Customer_Contract_Report)", "ServiceHistoryReport", gen.Session("user"))
            Return "OK"
        End Function


        Public Shared Function GetCCBILLcondition(ByVal ccontact_allAlias As String, ByVal billing_accountAlias As String) As String
            Dim ret As String = "[BILLINGACCOUNTALIAS].customer_id (+)= [CCONTACTALLALIAS].customer_id and [BILLINGACCOUNTALIAS].primary_flag (+)= 'X' " & _
                  vbNewLine & "and (([CCONTACTALLALIAS].ccseq = nvl([BILLINGACCOUNTALIAS].contact_seqno_temp, [BILLINGACCOUNTALIAS].contact_seqno)) or ([CCONTACTALLALIAS].ccbill = 'X' and [BILLINGACCOUNTALIAS].contact_seqno is null and [BILLINGACCOUNTALIAS].contact_seqno_temp is null))"
            ret = ret.Replace("[CCONTACTALLALIAS]", ccontact_allAlias)
            ret = ret.Replace("[BILLINGACCOUNTALIAS]", billing_accountAlias)

            Return ret
        End Function

        Public Shared Function FillService() As DataTable
            Dim SQL As String

            SQL = " select sncode,des " & _
                    vbNewLine & "from mpusntab " & _
                    vbNewLine & "where  des NOT LIKE 'NOT%'"


            Return (persistent.myDBConn.myORATable(SQL))
        End Function

    End Class

End Namespace
